"""
Case Type   : PACKAGE
Case Name   : 两个会话使用同一个包并对包内变量赋不同的值，旧会话赋值新会话查询不到
Create At   : 2024/7/4
Owner       : liu-tong-8848
Description :
    1.声明包规格和变量
    2.创建两个会话分别对包内同一变量赋不同的值
    3.旧会话的赋值新会话查询不到
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
"""

import os
import unittest
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PACKAGE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.package = 'p_package_test_0030'
        self.procedure = 'p_procedure_test1_0030'
        self.table = 't_table_test_0030'

    def test_package(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:声明包规格和变量 expect:成功'
        self.logger.info(step)
        create_package_msg = self.primary_sh.execut_db_sql(
            f'''create or replace package {self.package} AS 
                g_var NUMBER;
                PROCEDURE set_var(p_value NUMBER);
                FUNCTION get_var RETURN NUMBER;
                END {self.package};''')
        self.assertIn(self.Con.create_package_success_msg, create_package_msg, '执行失败' + step)
        create_package_body_msg = self.primary_sh.execut_db_sql(
            f'''create or replace package body {self.package} AS
                PROCEDURE set_var(p_value NUMBER) IS
                BEGIN
                g_var := p_value;
                END set_var;
                FUNCTION get_var RETURN NUMBER IS
                BEGIN
                    RETURN g_var;
                END get_var;
                END {self.package};''')
        self.assertIn(self.Con.create_package_body_success_msg, create_package_body_msg, '执行失败' + step)

        step = 'step2:创建两个会话分别对包内同一变量赋不同的值 expect:成功'
        self.logger.info(step)
        set_var_msg = self.primary_sh.execut_db_sql(
            f'''select {self.package}.set_var(10);
                select {self.package}.get_var();''')
        self.logger.info(set_var_msg)
        self.assertIn("10", set_var_msg, '执行失败' + step)

        step = 'step3:旧会话的赋值新会话查询不到 expect:合理报错'
        self.logger.info(step)
        get_var_msg = self.primary_sh.execut_db_sql(
            f'''select {self.package}.get_var();''')
        self.logger.info(get_var_msg)
        self.assertNotIn("10", get_var_msg, '执行失败' + step)

    def tearDown(self):
        step = 'step4:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_package_msg = self.primary_sh.execut_db_sql(
            f'''drop package {self.package};''')
        self.assertIn(self.Con.drop_package_success_msg, clean_package_msg, '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
